package org.sysdance.aplicativo.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.sysdance.aplicativo.entidade.Responsavel;
import org.sysdance.aplicativo.excecao.ConexaoException;
import org.sysdance.aplicativo.excecao.DAOException;
import org.sysdance.aplicativo.integracao.ResponsavelIntegracao;
import org.sysdance.aplicativo.util.ConexaoUtil;
import org.sysdance.aplicativo.util.ResultSetUtil;
import org.sysdance.aplicativo.util.SysDanceUtil;

public class ResponsavelDAO implements ResponsavelIntegracao {

	@Override
	public Responsavel inserirResponsavel(Responsavel responsavel) {
		final StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO responsavel(id_responsavel, id_cidade, id_aluno, id_grau_parentesco, nm_responsavel, cpf_responsavel, rg_responsavel, endereco_responsavel) ");
        sql.append("VALUES (NEXTVAL('responsavel_id_responsavel_seq'), ?, ?, ?, ?, ?, ?, ?);");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString(),new String[]{"id_responsavel"});
            ps.setInt(1, responsavel.getCidade().getIdCidade());
            ps.setInt(2, responsavel.getAluno().getIdAluno());
            ps.setInt(3,responsavel.getGrauParentesco().getIdGrauParentesco());
            ps.setString(4,responsavel.getNmResponsavel());
            ps.setString(5,responsavel.getCpfResponsavel());
            ps.setString(6,responsavel.getRgResponsavel());
            ps.setString(7,responsavel.getEnderecoResponsavel());
            ps.executeUpdate();
            final ResultSet rs = ps.getGeneratedKeys();
            if(rs.next()){
                responsavel.setIdResponsavel(rs.getInt(rs.getMetaData().getColumnName(1)));
            }
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return responsavel;
	}
	
	@Override
	public void atualizarDadosResponsavel(Responsavel responsavel)throws DAOException {
		final StringBuilder sql = new StringBuilder();
        sql.append("UPDATE responsavel SET id_cidade=?,id_grau_parentesco=?, nm_responsavel=?, cpf_responsavel=?, rg_responsavel=?, endereco_responsavel=? ");
        sql.append("WHERE id_responsavel=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setInt(1, responsavel.getIdCidade());
            ps.setInt(2, responsavel.getIdGrauParentesco());
            ps.setString(3, responsavel.getNmResponsavel());
            ps.setString(4, responsavel.getCpfResponsavel());
            ps.setString(5, responsavel.getRgResponsavel());
            ps.setString(6, responsavel.getEnderecoResponsavel());
            ps.setInt(7, responsavel.getIdResponsavel());
            ps.executeUpdate();
        } catch (final ConexaoException ex) {
           throw new DAOException(ex.getMessage());
        } catch (final SQLException sqlex) {
        	throw new DAOException(sqlex.getMessage());
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
	}
	
	@Override
	public void excluirRegistroResponsavel(Integer idAluno) throws DAOException {
		final StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM responsavel WHERE id_aluno=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setObject(1, idAluno);
            ps.executeUpdate();
        } catch (final ConexaoException ex) {
           throw new DAOException(ex.getMessage());
        } catch (final SQLException sqlex) {
        	throw new DAOException(sqlex.getMessage());
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
	}
	
	@Override
	public Responsavel consultarResponsavel(Integer idAluno) {
		final StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM responsavel WHERE id_aluno=?;");
        try {
            final Connection conexao = ConexaoUtil.criarNovaConexao();
            final PreparedStatement ps = conexao.prepareStatement(sql.toString());
            ps.setInt(1, idAluno);
            return ResultSetUtil.extrairUnicoRegistro(ps.executeQuery(), Responsavel.class);
        } catch (final ConexaoException ex) {
            SysDanceUtil.getInstancia().imprimirLog(ex);
        } catch (final SQLException sqlex) {
            SysDanceUtil.getInstancia().imprimirLog(sqlex);
        }finally{
        	try {
				ConexaoUtil.fecharConexao();
			} catch (ConexaoException e) {
				SysDanceUtil.getInstancia().imprimirLog(e);
			}
        }
        return null;
	}

}
